package databasetools;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.Iterator;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

//import org.apache.poi.hssf.usermodel.HSSFCell;
//import org.apache.poi.hssf.usermodel.HSSFRow;
//import org.apache.poi.hssf.usermodel.HSSFSheet;
//import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class App {

//    public static void readXLSFile() throws IOException {
//        InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls");
//        HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
//
//        HSSFSheet sheet = wb.getSheetAt(0);
//        HSSFRow row;
//        HSSFCell cell;
//
//        Iterator rows = sheet.rowIterator();
//
//        while (rows.hasNext()) {
//            row = (HSSFRow) rows.next();
//            Iterator cells = row.cellIterator();
//
//            while (cells.hasNext()) {
//                cell = (HSSFCell) cells.next();
//
//                if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
//                    System.out.print(cell.getStringCellValue() + " ");
//                } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
//                    System.out.print(cell.getNumericCellValue() + " ");
//                } else {
//                    //U Can Handel Boolean, Formula, Errors
//                }
//            }
//            System.out.println();
//        }
//
//    }
//    public static void writeXLSFile() throws IOException {
//
//        String excelFileName = "C:/Test.xls";//name of excel file
//
//        String sheetName = "Sheet1";//name of sheet
//
//        HSSFWorkbook wb = new HSSFWorkbook();
//        HSSFSheet sheet = wb.createSheet(sheetName);
//
//        //iterating r number of rows
//        for (int r = 0; r < 5; r++) {
//            HSSFRow row = sheet.createRow(r);
//
//            //iterating c number of columns
//            for (int c = 0; c < 5; c++) {
//                HSSFCell cell = row.createCell(c);
//
//                cell.setCellValue("Cell " + r + " " + c);
//            }
//        }
//
//        FileOutputStream fileOut = new FileOutputStream(excelFileName);
//
//        //write this workbook to an Outputstream.
//        wb.write(fileOut);
//        fileOut.flush();
//        fileOut.close();
//    }
    public static void readXLSXFile() throws IOException {
        InputStream ExcelFileToRead = new FileInputStream("c:\\800_fix\\400_JavaDev\\etcResources\\AvgDailyTempHH.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);

        //XSSFWorkbook test = new XSSFWorkbook();
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        XSSFCell cell;

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext()) {
            row = (XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            while (cells.hasNext()) {
                cell = (XSSFCell) cells.next();

                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    System.out.print(cell.getStringCellValue() + " ");
                } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    System.out.print(cell.getNumericCellValue() + " ");
                } else {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            System.out.println();
        }

    }

    public static void excelWeatherDataToDatabase() throws IOException {
        InputStream ExcelFileToRead = new FileInputStream("c:\\800_fix\\400_JavaDev\\etcResources\\AvgDailyTempHH.xlsx");
        XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);

        //XSSFWorkbook test = new XSSFWorkbook();
        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row;
        Iterator rows = sheet.rowIterator();
        rows.next(); //skip header row

        EntityManagerFactory emf = Persistence.createEntityManagerFactory("DatabaseTools");
        EntityManager em = emf.createEntityManager();
        EntityTransaction tx = em.getTransaction();
        tx.begin();

        while (rows.hasNext()) {

            row = (XSSFRow) rows.next();
            Weatherhh w = new Weatherhh();
            Double d = row.getCell(0).getNumericCellValue();
            w.setRowId(d.intValue());
            d = row.getCell(1).getNumericCellValue();
            w.setIntDate(d.intValue());
            String s = row.getCell(2).getStringCellValue();
            w.setTemperature(new BigDecimal(s));
            em.persist(w);
            System.out.println(w.toString());
        }
        tx.commit();

    }

    public static void writeXLSXFile() throws IOException {

        String excelFileName = "c:\\800_fix\\950_download\\Test.xlsx";//name of excel file

        String sheetName = "Sheet1";//name of sheet

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName);

        //iterating r number of rows
        for (int r = 0; r < 5; r++) {
            XSSFRow row = sheet.createRow(r);

            //iterating c number of columns
            for (int c = 0; c < 5; c++) {
                XSSFCell cell = row.createCell(c);

                cell.setCellValue("Cell " + r + " " + c);
            }
        }

        FileOutputStream fileOut = new FileOutputStream(excelFileName);

        //write this workbook to an Outputstream.
        wb.write(fileOut);
        fileOut.flush();
        fileOut.close();
    }

    public static void main(String[] args) throws IOException {

//        writeXLSFile();
//        readXLSFile();
//        writeXLSXFile();
//        readXLSXFile();
//        excelWeatherDataToDatabase();
//        DatabaseReader dbReader = new DatabaseReader();
//        dbReader.readData();

    }

    public void persist(Object object) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("DatabaseTools");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        try {
            em.persist(object);
            em.getTransaction().commit();
        } catch (Exception e) {
            e.printStackTrace();
            em.getTransaction().rollback();
        } finally {
            em.close();
        }
    }

}
